﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using System.Globalization;
using System.Transactions;
using CMS.Core.Libs;

namespace _4T.DAL
{
    public class ProfileInfoDAL
    {
        public ProfileInfoDAL() { }


        public List<ProfileInfo> GetProfileList()
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter da;
                DataTable dt;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileInfoGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    dt = new DataTable();
                    da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<ProfileInfo> list = new List<ProfileInfo>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        ProfileInfo p = new ProfileInfo(dr[0].ToString());
                        list.Add(p);
                    }

                    return list;
                }
            }

            return null;
        }

        public List<ProfileInfo> GetProfileList(string position, string personalCode, int stage, int status)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter da;
                DataTable dt;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileInfoGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Position", SqlDbType.VarChar, 50).Value = position;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;
                    cmd.Connection = conn;
                    dt = new DataTable();
                    da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<ProfileInfo> list = new List<ProfileInfo>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        ProfileInfo p = new ProfileInfo(dr[0].ToString());
                        list.Add(p);
                    }

                    return list;
                }
            }

            return null;
        }

        public int InsertProfileInfo(string profileCode, int patientInfo, int medicalInfo, int infoSupplierInfo, string note, string userCreated, DateTime? createdDate = null)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileInfoInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Parameters.Add("@PatientInfo", SqlDbType.Int).Value = patientInfo;
                    cmd.Parameters.Add("@MedicalInfo", SqlDbType.Int).Value = medicalInfo;
                    cmd.Parameters.Add("@InfoSupplierInfo", SqlDbType.Int).Value = infoSupplierInfo;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = userCreated;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = createdDate;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public int InsertProfileInfoForVideo(string profileCode, string note)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileInfoInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Parameters.Add("@Note", SqlDbType.NText).Value = note;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public List<ProfileStatus> SimpleSearch(string searchText)
        {

            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "SimpleSearch";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("searchText", SqlDbType.NVarChar, 150).Value = searchText;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<ProfileStatus> list = new List<ProfileStatus>();
                        //while (reader.Read())
                        //{
                        //    ProfileStatus item = GetProfileStatusFromReader(reader);
                        //    list.Add(item);
                        //}
                        using (IDataReader IReader = reader)
                        {
                            list = IReader.DatareaderToList(n => new ProfileStatus
                            {
                                ProfileCode = reader["ProfileCode"] is DBNull ? string.Empty : reader["ProfileCode"].ToString(),
                                PatientName = reader["PatientName"] is DBNull ? string.Empty : reader["PatientName"].ToString()
                            }).ToList();
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }


        public List<ProfileStatus> GetAllProfile()
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileGetListForCMS_NoPaging";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<ProfileStatus> list = new List<ProfileStatus>();
                        using (IDataReader IReader = reader)
                        {
                            list = IReader.DatareaderToList(n => new ProfileStatus
                            {
                                ProfileCode = reader["ProfileCode"] is DBNull ? string.Empty : reader["ProfileCode"].ToString(),
                                Stage = reader["Stage"] is DBNull ? 0 : Convert.ToInt32(reader["Stage"]),
                                Status = reader["Status"] is DBNull ? 0 : Convert.ToInt32(reader["Status"]),
                                Proposer = reader["Proposer"] is DBNull ? string.Empty : reader["Proposer"].ToString(),
                                Filter = reader["Filter"] is DBNull ? string.Empty : reader["Filter"].ToString(),
                                Verifier = reader["Verifier"] is DBNull ? string.Empty : reader["Verifier"].ToString(),
                                Approver = reader["Approver"] is DBNull ? string.Empty : reader["Approver"].ToString(),
                                PaymentPerson = reader["PaymentPerson"] is DBNull ? string.Empty : reader["PaymentPerson"].ToString(),
                                CreatedDate = reader["LastEditTime"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(reader["LastEditTime"]),
                                PatientName = reader["PatientName"] is DBNull ? string.Empty : reader["PatientName"].ToString()
                            }).ToList();
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        public List<ProfileStatus> GetListProfileWithPaging(int page, int pageSize, string searchText, int status)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@page", SqlDbType.Int).Value = page;
                    cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                    cmd.Parameters.Add("@searchText", SqlDbType.VarChar, 50).Value = searchText;
                    cmd.Parameters.Add("@status", SqlDbType.Int, 1).Value = status;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<ProfileStatus> list = new List<ProfileStatus>();
                        while (reader.Read())
                        {
                            ProfileStatus item = GetProfileStatusFromReader(reader);
                            list.Add(item);
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        public List<ProfileInfoAndStatus> GetListProfileInfoAndStatus(int page, int pageSize, string searchText, int status)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@page", SqlDbType.Int).Value = page;
                    cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                    cmd.Parameters.Add("@searchText", SqlDbType.VarChar, 50).Value = searchText;
                    cmd.Parameters.Add("@status", SqlDbType.Int, 1).Value = status;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<ProfileInfoAndStatus> list = new List<ProfileInfoAndStatus>();
                        while (reader.Read())
                        {
                            ProfileInfoAndStatus item = GetProfileInfoAndStatusFromReader(reader);
                            list.Add(item);
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        public int GetTotalNumberProfile(int status)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileGetTotalNumber";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@status", SqlDbType.Int, 1).Value = status;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public string GetProfileInfoCodeFromProfileCode(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                string profileInfoCode = string.Empty;
                try
                {
                    conn.Open();
                    cmd.CommandText = "GetProfileInfoCode";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@profileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;
                    profileInfoCode = Convert.ToString(cmd.ExecuteScalar());
                    //reader = cmd.ExecuteReader();
                    //profileInfoCode = reader["profileCode"].ToString();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return profileInfoCode;
            }

            return string.Empty;
        }

        private ProfileStatus GetProfileStatusFromReader(SqlDataReader reader)
        {
            string profileCode = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                profileCode = reader.GetString(1);

            int stage = 0;
            if (reader.GetValue(2) != DBNull.Value)
                stage = reader.GetInt32(2);

            int status = 0;
            if (reader.GetValue(3) != DBNull.Value)
                status = reader.GetInt32(3);

            string proposer = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                proposer = reader.GetString(4);

            string filter = string.Empty;
            if (reader.GetValue(5) != DBNull.Value)
                filter = reader.GetString(5);

            string verifier = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                verifier = reader.GetString(6);

            string approver = string.Empty;
            if (reader.GetValue(7) != DBNull.Value)
                approver = reader.GetString(7);

            string amount = string.Empty;
            if (reader.GetValue(8) != DBNull.Value)
                amount = reader.GetString(8);

            string note = string.Empty;
            if (reader.GetValue(9) != DBNull.Value)
                note = reader.GetString(9);

            string patientName = string.Empty;
            if (reader.GetValue(13) != DBNull.Value)
                patientName = reader.GetString(13);

            string paymentPerson = string.Empty;
            if (reader.GetValue(11) != DBNull.Value)
                paymentPerson = reader.GetString(11);

            return new ProfileStatus(profileCode, stage, status, proposer, filter, verifier, approver, amount, note, patientName, paymentPerson);
        }

        private ProfileInfoAndStatus GetProfileInfoAndStatusFromReader(SqlDataReader reader)
        {
            string profileCode = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                profileCode = reader.GetString(1);
            int stage = 0;
            if (reader.GetValue(2) != DBNull.Value)
                stage = reader.GetInt32(2);
            int status = 0;
            if (reader.GetValue(3) != DBNull.Value)
                status = reader.GetInt32(3);
            string proposer = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                proposer = reader.GetString(4);
            string filter = string.Empty;
            if (reader.GetValue(5) != DBNull.Value)
                filter = reader.GetString(5);
            string verifier = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                verifier = reader.GetString(6);
            string approver = string.Empty;
            if (reader.GetValue(7) != DBNull.Value)
                approver = reader.GetString(7);
            string amount = string.Empty;
            if (reader.GetValue(8) != DBNull.Value)
                amount = reader.GetString(8);
            string note = string.Empty;
            if (reader.GetValue(9) != DBNull.Value)
                note = reader.GetString(9);
            string editTime = string.Empty;
            if (reader.GetValue(10) != DBNull.Value)
                editTime = reader.GetDateTime(10).ToString("G", CultureInfo.CreateSpecificCulture("vi-VI"));
            string paymentPerson = string.Empty;
            if (reader.GetValue(11) != DBNull.Value)
                paymentPerson = reader.GetString(11);
            string paymentAmount = string.Empty;
            if (reader.GetValue(12) != DBNull.Value)
                paymentAmount = reader.GetString(12);
            string patientName = string.Empty;
            if (reader.GetValue(13) != DBNull.Value)
                patientName = reader.GetString(13);
            bool binRecycle = false;
            if (reader.GetValue(14) != DBNull.Value)
                binRecycle = (reader.GetInt32(14) == 1 ? true : false);

            return new ProfileInfoAndStatus(profileCode, stage, status, proposer, patientName, filter, verifier, approver, amount, note, editTime, paymentPerson, paymentAmount, binRecycle);
        }

        public CMS_ProfileInfo GetLastProfileInfoByProfileCode(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                CMS_ProfileInfo profileInfo = null;
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader = null;
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "ProfileInfoGetByProfileCode";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                        cmd.Connection = conn;

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            reader.Read();
                            profileInfo = GetProfileInfoFromReader(reader);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }

                return profileInfo;
            }

            return null;
        }

        public CMS_ProfileInfo GetProfileInfoByProfileInfoCode(string profileInfoCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                CMS_ProfileInfo profileInfo = null;
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    SqlConnection conn = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader = null;
                    try
                    {
                        conn.Open();
                        cmd.CommandText = "ProfileInfoGetByProfileInfoCode";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileInfoCode;
                        cmd.Connection = conn;

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            reader.Read();
                            profileInfo = GetProfileInfoFromReader(reader);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                    scope.Complete();
                }

                return profileInfo;
            }

            return null;
        }

        private CMS_ProfileInfo GetProfileInfoFromReader(SqlDataReader reader)
        {
            int profileInfoID = 0;
            if (reader.GetValue(0) != DBNull.Value)
                profileInfoID = reader.GetInt32(0);

            string profileCode = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                profileCode = reader.GetString(1);

            string profileInfoCode = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                profileInfoCode = reader.GetString(2);

            int patientInfo = 0;
            if (reader.GetValue(3) != DBNull.Value)
                patientInfo = reader.GetInt32(3);

            int medicalInfo = 0;
            if (reader.GetValue(4) != DBNull.Value)
                medicalInfo = reader.GetInt32(4);

            int infoSupplierInfo = 0;
            if (reader.GetValue(5) != DBNull.Value)
                infoSupplierInfo = reader.GetInt32(5);

            string note = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                note = reader.GetString(6);

            string lastEditTime = string.Empty;
            if (reader.GetValue(7) != DBNull.Value)
                lastEditTime = reader.GetDateTime(7).ToString("G", CultureInfo.CreateSpecificCulture("vi-VI"));

            return new CMS_ProfileInfo(profileInfoID, profileCode, profileInfoCode, patientInfo, medicalInfo, infoSupplierInfo, lastEditTime);
        }

        public List<CMS_ProfileInfo> GetListProfileInfoByProfileCode(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileInfoGetListByProfileCode";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader != null)
                    {
                        List<CMS_ProfileInfo> result = new List<CMS_ProfileInfo>();
                        while (reader.Read())
                        {
                            CMS_ProfileInfo info = GetProfileInfoFromReader(reader);
                            result.Add(info);
                        }
                        return result;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        public List<CMS_ProfileFilter> GetListProfileFilter(int page, int pageSize, string searchText)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileFilterGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("page", SqlDbType.Int).Value = page;
                    cmd.Parameters.Add("pageSize", SqlDbType.Int).Value = pageSize;
                    cmd.Parameters.Add("searchText", SqlDbType.VarChar, 50).Value = searchText;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader != null)
                    {
                        List<CMS_ProfileFilter> result = new List<CMS_ProfileFilter>();
                        while (reader.Read())
                        {
                            CMS_ProfileFilter info = GetProfileFilterListFromReader(reader);
                            result.Add(info);
                        }
                        return result;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        private CMS_ProfileFilter GetProfileFilterListFromReader(SqlDataReader reader)
        {

            string profileCode = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                profileCode = reader.GetString(1);

            string profileInfoCode = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                profileInfoCode = reader.GetString(2);

            string proposer = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                proposer = reader.GetString(3);

            string proposeTime = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                proposeTime = reader.GetDateTime(4).ToString("G", CultureInfo.CreateSpecificCulture("vi-VI"));

            return new CMS_ProfileFilter(profileCode, profileInfoCode, proposer, proposeTime);
        }
    }
}